﻿using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace Coldairarrow.Util.Helper
{
    public class DataTableToExcel
    {

        #region---第一版导出EXCEL文件代码----

        /// <summary>
        /// DateTable导出Excel
        /// </summary>
        /// <param name="dt">数据dt</param>
        /// <param name="strExcelFileName">保存文件名称</param>
        /// <param name="rowNameList">标题名称列表</param>
        /// <param name="sheetName">表名</param>
        /// <param name="forderName">保存文件夹名</param>
        /// <returns></returns>
        public string datatableToExcel(DataTable dt, string strExcelFileName, String[] rowNameList, string sheetName, string forderName,string rootPath)
        {
            //建立空白工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();//工作簿实例
            //在工作簿中：建立空白工作表
            ISheet sheet = workbook.CreateSheet(sheetName);
            //在工作表1中：建立行，参数为行号，从0计
            IRow row = sheet.CreateRow(0);

            //表头样式
            ICellStyle HeadercellStyle = workbook.CreateCellStyle();
            HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            IFont headerfont = workbook.CreateFont();
            headerfont.IsBold = true; //标题加粗
            //headerfont.Boldweight = (short)FontBoldWeight.Bold;//加粗旧写法
            HeadercellStyle.SetFont(headerfont);

            //设置第一行表头
            int icolIndex = 0;
            IRow headerRow = sheet.CreateRow(0);
            foreach (string rowName in rowNameList)
            {
                if (icolIndex < rowNameList.Length)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(rowName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }
            }

            //内容行样式
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.FontName = "宋体";
            cellfont.FontHeightInPoints = 10;
            cellStyle.SetFont(cellfont);
            //设置单元格格式
            IDataFormat format = workbook.CreateDataFormat();
            cellStyle.DataFormat = format.GetFormat("0");

            //调色板
            HSSFPalette palette = workbook.GetCustomPalette();
            palette.SetColorAtIndex(8, 184, 204, 228);

            //建立内容行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow DataRow1 = sheet.CreateRow(i + 1);
                //14 8
                //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Type dataType = dt.Columns[j].DataType;
                    if (dataType.Name == "DateTime")
                    {
                        string text = "";
                        var val = (DateTime?)dt.Rows[i][j];
                        if (val.HasValue && ((DateTime)val).Year != 1)
                        {
                            text = ((DateTime)val).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        
                        ICell cell = DataRow1.CreateCell(j);
                        cell.SetCellValue(text);
                        cell.CellStyle = cellStyle;
                    }
                    else  if (dataType.Name == "Int32")
                    {
                        string text = dt.Rows[i][j].IsNullOrEmpty()?"0":dt.Rows[i][j].ToString();
                        ICell cell = DataRow1.CreateCell(j);
                        cell.SetCellValue(text);
                        cell.CellStyle = cellStyle;
                    }
                    else
                    {
                        string text = dt.Rows[i][j].IsNullOrEmpty() ? "" : dt.Rows[i][j].ToString();
                        ICell cell = DataRow1.CreateCell(j);
                        cell.SetCellValue(text);
                        cell.CellStyle = cellStyle;
                    }              
                }
            }

            string savedName = strExcelFileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            string fileName = GetPath2(forderName, rootPath) + "/" + savedName;




            //保存在文件流
            FileStream file = new FileStream(fileName, FileMode.OpenOrCreate);
            workbook.Write(file);
            file.Flush();
            file.Close();

            var sNum = fileName.LastIndexOf("wwwroot");
            var endNum = fileName.Length;

            fileName = fileName.Substring(sNum , (endNum - sNum));

            //var path = GetPath(forderName) + "/" + savedName;
            return fileName;
        }

        public string OrderdatatableToExcel(DataTable dt,List<RSpanDTO> spanList, string strExcelFileName, String[] rowNameList, string sheetName, string forderName, string rootPath)
        {
            //建立空白工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();//工作簿实例
            //在工作簿中：建立空白工作表
            ISheet sheet = workbook.CreateSheet(sheetName);
            //在工作表1中：建立行，参数为行号，从0计
            IRow row = sheet.CreateRow(0);

            //表头样式
            ICellStyle HeadercellStyle = workbook.CreateCellStyle();
            HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            IFont headerfont = workbook.CreateFont();
            headerfont.IsBold = true; //标题加粗
            //headerfont.Boldweight = (short)FontBoldWeight.Bold;//加粗旧写法
            HeadercellStyle.SetFont(headerfont);

            //设置第一行表头
            int icolIndex = 0;
            IRow headerRow = sheet.CreateRow(0);
            foreach (string rowName in rowNameList)
            {
                if (icolIndex < rowNameList.Length)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(rowName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }
            }

            //内容行样式
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
            //cellStyle.Alignment = HorizontalAlignment.Center;//水平居中
            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.FontName = "宋体";
            cellfont.FontHeightInPoints = 10;
            cellStyle.SetFont(cellfont);
            //设置单元格格式
            IDataFormat format = workbook.CreateDataFormat();
            cellStyle.DataFormat = format.GetFormat("0");

            //调色板
            HSSFPalette palette = workbook.GetCustomPalette();
            palette.SetColorAtIndex(8, 184, 204, 228);

            int m = 0;
            //建立内容行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow DataRow1 = sheet.CreateRow(i + 1);
                //14 8
                //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域

                //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                   
                    
                    Type dataType = dt.Columns[j].DataType;

                    string text = dt.Rows[i][j].IsNullOrEmpty() ? "" : dt.Rows[i][j].ToString();
                    ICell cell = DataRow1.CreateCell(j);
                    cell.SetCellValue(text);
                    cell.CellStyle = cellStyle;
                    if (j < 31 && m < spanList.Count && (i - 1) == spanList[m].StartRow)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(i, i + spanList[m].SpanRow, j, j));
                        if (j == 30)
                        {
                            m++;
                        }
                    }

                }
            }

            string savedName = strExcelFileName + ".xls";
            string fileName = GetPath2(forderName, rootPath) + "/" + savedName;




            //保存在文件流
            FileStream file = new FileStream(fileName, FileMode.OpenOrCreate);
            workbook.Write(file);
            file.Flush();
            file.Close();

            var sNum = fileName.LastIndexOf("wwwroot");
            var endNum = fileName.Length;

            fileName = fileName.Substring(sNum + 7, (endNum - sNum - 7));

            return fileName;
        }


        /// <summary>
        /// 获取文件相对路径
        /// </summary>
        /// <param name="forderName"></param>
        /// <returns></returns>
        private string GetPath(string forderName)
        {
            string path = "/" + forderName + "/" + DateTime.Now.ToString("yyyy-MM-dd");
           
            return path;
        }

        /// <summary>
        /// 获取文件保存路径
        /// </summary>
        /// <param name="forderName"></param>
        /// <param name="rootPath"></param>
        /// <returns></returns>
        private string GetPath2(string forderName, string rootPath)
        {
            string path = rootPath + "/Download/" + forderName + "/" + DateTime.Now.ToString("yyyy-MM-dd");
            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }
               
            return path;
        }

        #endregion

        public class RSpanDTO
        {
            public int StartRow { get; set; }
            public int SpanRow { get; set; }
        }


    }
}
